package database;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.Circuit;
import model.Session;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class ConectorDB {
	static String userName;
	static String password;
	static String db;
	static int port;
	static String url = "jdbc:mysql://db4free.net";
	static Connection conn = null;
	static Statement s;
    
	public ConectorDB(String usr, String pass, String db, int port) {
		ConectorDB.userName = usr;
		ConectorDB.password = pass;
		ConectorDB.db = db;
		ConectorDB.port = port;
		ConectorDB.url += ":"+port+"/";
		ConectorDB.url += db;
	}

	/**
	 * Conecta amb le bbdd
	 */
    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Connection");
            conn = (Connection) DriverManager.getConnection(url, userName, password);
            if (conn != null) {
                System.out.println("Conexi��� a base de dades "+url+" ... Ok");
            }
        }
        catch(SQLException ex) {
            System.out.println("Problema al connecta-nos a la BBDD --> "+url);
            System.out.println(ex);
        }
        catch(ClassNotFoundException ex) {
            System.out.println(ex);
        }

    }
    
    /**
     * Insereix a la bbdd
     * @param query
     * @return true si s'ha inserit, false si no.
     */
    public boolean insertQuery(String query){
        try {
            s =(Statement) conn.createStatement();
            s.executeUpdate(query);
            return true;

        } catch (SQLException ex) {
            System.out.println("Problema al Inserir --> " + ex.getSQLState());
        }

        return false;
       
    }
    
    /**
     * Modifica a la bbdd
     * @param query
     */
    public void updateQuery(String query){
    	 try {
             s =(Statement) conn.createStatement();
             s.executeUpdate(query);

         } catch (SQLException ex) {
             System.out.println("Problema al Modificar --> " + ex.getSQLState());
         }
    }
    
    /**
     * Elimina a la bbdd
     * @param query
     */
    public void deleteQuery(String query){
    	 try {
             s =(Statement) conn.createStatement();
             s.executeUpdate(query);
             
         } catch (SQLException ex) {
             System.out.println("Problema al Eliminar --> " + ex.getSQLState());
         }
    	
    }
    
    /**
     * Recupera de la bbdd
     * @param query
     * @return la consulta.
     */
    public ResultSet selectQuery(String query){
    	ResultSet rs = null;
    	 try {
             s =(Statement) conn.createStatement();
             rs = s.executeQuery (query);
             
         } catch (SQLException ex) {
             System.out.println("Problema al Recuperar les dades --> " + ex.getSQLState());
         }
		return rs;
    }
    
    /**
     * Desconceta el programa de la base de dades.
     */
    public void disconnect(){
    	try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("Problema al tancar la connexi��� --> " + e.getSQLState());
		}
    }
    
    /**
     * Comprova si l'usuari i contraseña es troben a la base de dades.
     * @param user
     * @param pass
     * @return true si s'hi troben, false si no.
     */
    public boolean checkLogin(String user, String pass){
    	
    	ResultSet consulta;
    	
    	consulta = selectQuery("SELECT * FROM Users");
    	
    	try {
			while (consulta.next())
			{
			   if(user.equals(consulta.getObject("Username")) && pass.equals(consulta.getObject("Password"))){
				   return true;
			   }
			}
		} catch (SQLException e) {
			System.out.println("Error!");
		}
    	
    	return false;
    }
    
    /**
     * Comprova si l'usuari es troba a la base de dades.
     * @param user
     * @return true si s'hi troba, false si no.
     */
    public boolean checkUser(String user){
    	
    	ResultSet consulta;
    	
    	consulta = selectQuery("SELECT * FROM Users");
    	
    	try {
			while (consulta.next())
			{
			   if(user.equals(consulta.getObject("Username"))){
				   return true;
			   }
			}
		} catch (SQLException e) {
			System.out.println("Error!");
		}
    	
    	return false;
    }
    
    /**
     * Carrega la llista sessions amb les dades de cada sessió, donat un usuari i un circuit.
     * @param usuari
     * @param circuit
     * @param sessions
     * @return true si s'han carregat les dades, false si hi ha algun problema.
     */
    public boolean carregaResum(String usuari,String circuit, ArrayList<Session> sessions){
    	ResultSet consulta;
    	
    	consulta = selectQuery("SELECT * FROM Resume WHERE Username LIKE '" + usuari + "' AND Circuit LIKE '" + circuit + "'");
    	Session auxSession = new Session();
    	
    	try {
			while (consulta.next())
			{
				auxSession = new Session();
				auxSession.setName(consulta.getObject("SessionID").toString());
				auxSession.setUsername(consulta.getObject("Username").toString());
				auxSession.setCircuit(consulta.getObject("Circuit").toString());
				auxSession.setMinTime(Float.parseFloat(consulta.getObject("MinTime").toString()));
				auxSession.setAvgTime(Float.parseFloat(consulta.getObject("AvgTime").toString()));
				auxSession.setAvgTopSpeed(Float.parseFloat(consulta.getObject("Avg Top Speed").toString()));
				auxSession.setAvgAvgSpeed(Float.parseFloat(consulta.getObject("Avg Avg Speed").toString()));
				auxSession.setAvgMinSpeed(Float.parseFloat(consulta.getObject("Avg min speed").toString()));
				
				if (!sessions.contains(auxSession)) {
					sessions.add(auxSession);
				}
			}
			
			return true;
			
		} catch (SQLException e) {
			System.out.println("Error!");
		}
    	
    	return false;
    }
    
    /**
     * Recuperem el nom dels circuits carregats a la base de dades
     * @return l'array de Strings amb els noms dels circuits.
     */
    public String[] obtenirCircuits(String user) {
    	
    	String[] noms = new String[0];
    	int nCircuits = 0;
    	int i = 0;
    	ResultSet consulta = selectQuery("SELECT COUNT( Circuit ) as nCircuits FROM Resume WHERE Username LIKE '" + user +"'");
		
    	try {
			while (consulta.next())
			{
			   if (consulta.getObject("nCircuits") != null && !consulta.getObject("nCircuits").equals("0")){
				   nCircuits = Integer.parseInt(consulta.getObject("nCircuits").toString());
				   noms = new String[nCircuits];
				   consulta = selectQuery("SELECT DISTINCT Circuit FROM Resume WHERE Username LIKE '" + user +"'");
				   
				   while (consulta.next()) {
					   noms[i] = consulta.getObject("Circuit").toString();
					   i++;
				   }
			   }
			}
		} catch (SQLException e) {
			System.out.println("Error!");
		}
    	
    	return noms;
    }
    
    /**
     * Registra l'usuari a la bbdd
     * @param user
     * @param pass
     * @return true si s'ha inserit, false si no.
     */
    public boolean registraUsuari(String user, String pass) {
    	return insertQuery("INSERT INTO `Users`(`Username`, `Password`) VALUES ('" + user + "','" + pass + "')");
    }
    
    /**
     * S'encarrega de pujar les dades de la sessi� actual
     * @param usuari
     * @param circuit
     * @param sessions
     */
    public boolean pujaResum(String usuari,Circuit circuit, Session session){

    	return insertQuery("INSERT INTO `Resume`(`SessionID`, `Username`, `Circuit`, `MinTime`, " +
    			"`AvgTime`, `Avg Top Speed`, `Avg Avg Speed`, `Avg min speed`)" +
    			" VALUES ('" + session.getName() +"','" + session.getUsername() +"','" + session.getCircuit() + 
    			"','" + session.getMinTime() + "','" + session.getAvgTime() + "','" + session.getAvgTopSpeed() + 
    			"','" + session.getAvgAvgSpeed() +"','" + session.getAvgMinSpeed() + "')");
    	
    }

}
